Excel 2007 save as dbf4 (dbfIV)

B

bolo

Active Member
Joined
Mar 23, 2002
Messages
423
Right i have been forced to upgraded to Excel 2007 and i don't like it. It seems slower and the new menu bar isn't very helpful, especially with macros. But the thing that really gets me wound up is the fact that i now cannot save as a dfb file!!!! I do a fair bit of work in ArcGIS and so i have written macros to output dbf files for GIS work. But now i can't do it! anyone know of any simple code that would allow me to save as a dbf. and i know its quite late, but how can i complain to micro$oft? Its not as if the dbf save as function was causing anyone grief in the first place. This upgrade is seriously pi$$ing me off!

I tried searching but nothing came up, so apologies if it is a repeat thread.

Stupid Micro$oft... Rubbish!
 
Sort by date Sort by votes

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Andrew Poulsom said:
curPath must be the full name, including the path, of the database.
Click to expand...

Re Norie, yep i am trying to save as a dbf. THe csv is just an intermediate stop gap.

Re Andrew,

when i change curpath to be a full path + mdb filename i get a 3044 error. And i know the mdb file exists,,,, How can i access the current open database?
 
Upvote 0
This worked for me in Excel 2000. I don't have Excel 2007 here to test it.

Code: 
Sub saveDBF()
    Application.DisplayAlerts = False
    Dim curPath As String
    Dim FName As String
    Dim tempLen As Integer
    Dim FileN As Variant
    Dim FileNN As String
'   save current page as csv
    curPath = ThisWorkbook.Path & "\"
    FName = Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".")) & "csv"
    If FileN = "" Then
        FileN = Application.GetSaveAsFilename(InitialFileName:=FName, _
          FileFilter:="DBF 4 (dBASE IV) (*.csv),*.*", Title:="Save As DBF")
        If FileN = False Then Exit Sub
    End If
    If Len(Dir(FileN)) <> 0 Then Kill FileN
    tempLen = Len(curPath)
    FileNN = Right(FileN, Len(FileN) - tempLen)
    ActiveWorkbook.SaveAs Filename:=FileN, FileFormat:=xlCSV
    Windows(FileNN).Close
    FileNN = Left(FileNN, Len(FileNN) - 4)
    If Len(Dir(Left(FileN, Len(FileN) - 3) & "mdb")) <> 0 Then _
        Kill Left(FileN, Len(FileN) - 3) & "mdb"
'   create Access object
    Dim objAcc As Object
    Set objAcc = CreateObject("Access.Application")
    objAcc.Visible = True
    objAcc.NewCurrentDatabase Left(FileN, Len(FileN) - 3) & "mdb"
    objAcc.DoCmd.TransferText acImportDelim, , FileNN, FileN, True
    objAcc.DoCmd.TransferDatabase acExport, "dBase IV", curPath, _
       acTable, FileNN, FileNN & ".dbf", False
'   Close the database.
    objAcc.CloseCurrentDatabase
'   Quit Access.
    objAcc.Quit
'   Close the object variable + housekeeping.
    Set objAcc = Nothing
    Kill FileN
    Kill Left(FileN, Len(FileN) - 3) & "mdb"
    Application.DisplayAlerts = True
End Sub
 
Upvote 0
Andrew Poulsom said:
Oh dear! What is the exact text of the error? I'll have a look later when I have access to Office 2007.
Click to expand...

I am trying to save temp.dbf

runtime error 3011

The Microsoft Office Access database engine could not find the object 'temp'. Make sure the object exists and that you spell its name and the path name correctly.

really appreciate your help. It is so frustrating, because it should work, but i cannot for the life of me work out why it doesn't. THe help on transferdatabase is not much help either! Could i be missing a reference of some sort?
 
Upvote 0
This worked for me in Excel 2007:

Code: 
Sub saveDBF()
    Const acImportDelim As Integer = 0
    Const acExport As Integer = 1
    Const acTable As Integer = 0
    Dim curPath As String
    Dim FName As String
    Dim tempLen As Integer
    Dim FileN As Variant
    Dim FileNN As String
    Dim objAcc As Object
    Application.DisplayAlerts = False
'   save current page as csv
    curPath = ThisWorkbook.Path & "\"
    FName = Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".")) & "csv"
    If FileN = "" Then
        FileN = Application.GetSaveAsFilename(InitialFileName:=FName, _
          FileFilter:="DBF 4 (dBASE IV) (*.csv),*.*", Title:="Save As DBF")
        If FileN = False Then Exit Sub
    End If
    If Len(Dir(FileN)) <> 0 Then Kill FileN
    tempLen = Len(curPath)
    FileNN = Right(FileN, Len(FileN) - tempLen)
    ActiveWorkbook.SaveAs Filename:=FileN, FileFormat:=xlCSV
    Windows(FileNN).Close
    FileNN = Replace(FileNN, ".csv", "")
    If Len(Dir(Left(FileN, Len(FileN) - 3) & "accdb")) <> 0 Then _
        Kill Left(FileN, Len(FileN) - 3) & "accdb"
'   create Access object
    Set objAcc = CreateObject("Access.Application")
'    MsgBox objAcc.Version
    objAcc.Visible = True
    objAcc.NewCurrentDatabase Replace(FileN, ".csv", "")
    objAcc.DoCmd.TransferText acImportDelim, , FileNN, FileN, True
    objAcc.DoCmd.TransferDatabase acExport, "dBase IV", curPath, _
       acTable, FileNN, FileNN & ".dbf", False
'   Close the database.
    objAcc.CloseCurrentDatabase
'   Quit Access.
    objAcc.Quit
'   Close the object variable + housekeeping.
    Set objAcc = Nothing
    Kill FileN
    Kill Replace(FileN, ".csv", ".accdb")
    Application.DisplayAlerts = True
End Sub

I had trouble to start with because Access 2003 was opening rather than Access 2007 (I have both versions).

I used a small workbook named Data.xlsx containing:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17 width=64>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Value</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>b</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17>c</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>3</TD></TR></TBODY></TABLE>
 
Upvote 0
Thanks ANdrew that worked!!!!! I have no idea why my version didn't work. I suspect it was because i was referencing a access 03 database (mdb file) as opposed to a 07 one. However how was i supposed to know that! anyway you time and effort are much appreciated....
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Saving excel sheet into a .PDF using macro.
Replies
2
Views
131
Luke1690
L
S
  • Question
Macro: flatten sheet, delete other tab, and save
Replies
4
Views
218
steallan
S
E
  • Question
Old XLSM files crashing Excel; looking for help to troubleshoot, please
Replies
9
Views
1K
jkpieterse
jkpieterse
K
  • Locked
  • Solved
VBA Save Copy as csv complete with name and datestamp
Replies
6
Views
1K
Peter_SSs
Peter_SSs
N
  • Solved
Excel date format problem
Replies
6
Views
1K
nahean
N

Forum statistics

Threads
1,215,106
Messages
6,123,123
Members
449,096
Latest member
provoking

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top